﻿Imports System.Data.OleDb

Public Class PhotoGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM PhotoListView"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM Photo"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM Photo LEFT JOIN SiteUser " &
                   "ON Photo.UserID=SiteUser.ID " &
                   "WHERE Photo.ID=@ID"
        End Get
    End Property

    Public Function Insert(ByVal title As String, ByVal description As String, ByVal userID As ULong, Optional ByVal photo As String = "nophoto.png") As ULong
        Dim newID As ULong
        Dim sqlQuery As String = "INSERT INTO Photo (Title, Description, UserID, ImagePath) VALUES (@Title, @Description, @UserID, @ImagePath)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Title", title)
                command.Parameters.AddWithValue("@Description", description)
                command.Parameters.AddWithValue("@UserID", userID)
                command.Parameters.AddWithValue("@ImagePath", photo)
                connection.Open()
                command.ExecuteNonQuery()
                newID = GetLastInsertID(command)
            End Using
        End Using

        Return newID
    End Function

    Public Sub Update(ByVal photoID As ULong, ByVal title As String, ByVal description As String, Optional ByVal photo As String = Nothing)
        Dim sqlQuery As String
        Dim where As String = " WHERE ID=@PhotoID"
        If photo Is Nothing Then
            sqlQuery = "UPDATE Photo SET Title=@Title, Description=@Description" & where
        Else
            sqlQuery = "UPDATE Photo SET Title=@Title, Description=@Description, ImagePath=@Photo" & where
        End If
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Title", title)
                command.Parameters.AddWithValue("@Description", description)
                If photo IsNot Nothing Then
                    command.Parameters.AddWithValue("@Photo", photo)
                End If
                command.Parameters.AddWithValue("@PhotoID", photoID)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class
